package df.bean.process;

import java.sql.ResultSet;
import java.sql.SQLException;

import df.bean.db.conn.DBConnection;
import df.bean.obj.util.JDate;
import df.bean.obj.util.Variables;

public class ProcessImportOnWard {

    private DBConnection conn = new DBConnection();
    private DBConnection conn2 = new DBConnection();
    private String user_id = "";
    private ResultSet rs = null;
    private String invoiceNo = "";
    private String receiptTypeCode = "";
    private String hnNo = "";
    private String lineNo = "";
    private String episodeNo = "";
    
    public ProcessImportOnWard(){
    	this.conn.connectToLocal();
    	this.conn2.connectToLocal();
    }
    public void setUserId(String userId){
    	user_id = userId;
    }
    public boolean importOnward(String hospitalCode) {
		boolean result = false;
		int numAffRec = 0;
		String sql = "SELECT * " +
					 "FROM TRN_DAILY " +
					 "WHERE LINE_NO IN (SELECT LINE_NO FROM TRN_DAILY "+
					 "WHERE INVOICE_NO = HN_NO+EPISODE_NO+PAYOR_OFFICE_CODE AND "+
					 "IS_ONWARD = 'Y' AND HOSPITAL_CODE = '"+hospitalCode+"') AND "+
					 "IS_ONWARD = 'N' AND HOSPITAL_CODE = '"+hospitalCode+"'";
					 //"WHERE IS_ONWARD = 'N' ORDER BY INVOICE_NO ASC";
		System.out.print(Variables.IS_TEST ? sql+" <> Start : "+JDate.getTime()+"\n" : "");
		if(hospitalCode.equals("00001")){
			this.rs = this.conn.executeQuery(sql);
			System.out.print(Variables.IS_TEST ? "End : "+JDate.getTime()+"\n" : "");
			try {
				while(this.rs.next()){
					this.invoiceNo = this.rs.getString("INVOICE_NO");
					this.receiptTypeCode = this.rs.getString("RECEIPT_TYPE_CODE");
					this.hnNo = this.rs.getString("HN_NO");
					this.lineNo = this.rs.getString("LINE_NO");
					this.episodeNo = this.rs.getString("EPISODE_NO");
					if(this.isUpdate(this.invoiceNo, this.receiptTypeCode, this.hnNo,this.lineNo, this.episodeNo, hospitalCode)){
						if(this.isBackup(this.invoiceNo, this.receiptTypeCode, this.hnNo, this.lineNo, this.episodeNo, hospitalCode)){
							if(this.isDelete(this.invoiceNo, this.receiptTypeCode, this.hnNo, this.lineNo, this.episodeNo, hospitalCode)){
								numAffRec += 1;
							}
						}
					}
				}
			} catch (SQLException e){
				System.out.println("Error => " + e.getMessage());
			}finally{
				if(this.conn != null){
					this.conn.Close();
				}
				if(this.conn2 != null){
					this.conn2.Close();
				}
			}
				
			if(numAffRec > 0 ){
				result = true;
			}
		}else{
			result = true;
    	}
    	return result;
	}
    private boolean isUpdate(String invoiceNo, String receiptTypeCode, String hnNo, String lineNo, String episodeNo, String hospitalCode) {
    	boolean result = false;
    	int numAffRec = 0;
		String sqlComand = "";
		sqlComand = "UPDATE TRN_DAILY " +
					"SET INVOICE_NO = '"+invoiceNo+"' ,RECEIPT_TYPE_CODE = '"+receiptTypeCode+"' "+
					"WHERE LINE_NO = '"+lineNo+"' "+
					"AND EPISODE_NO = '"+episodeNo+"' " +
					"AND HN_NO ='"+hnNo+"' " +
					"AND IS_ONWARD = 'Y' " +
					"AND HOSPITAL_CODE = '"+hospitalCode+"' ";
		numAffRec = conn2.executeUpdate(sqlComand);
		if(numAffRec > 0){
			result = true;
		}
		return result;
	}
    private boolean isDelete(String invoiceNo, String receiptTypeCode, String hnNo, String lineNo, String episodeNo, String hospitalCode) {
    	boolean result = false;
    	int numAffRec = 0;
		String sqlComand = "";
		sqlComand = "DELETE FROM TRN_DAILY " +
					"WHERE " +
					"HOSPITAL_CODE = '"+hospitalCode+"' "+
					"AND LINE_NO = '"+lineNo+"' "+
					"AND EPISODE_NO = '"+episodeNo+"' " +
					"AND HN_NO ='"+hnNo+"' "+
					"AND IS_ONWARD = 'N' ";
		numAffRec = conn2.executeUpdate(sqlComand);
		if(numAffRec > 0){
			result = true;
		}
		return result;
	}
    private boolean isBackup(String invoiceNo, String receiptTypeCode, String hnNo, String lineNo, String episodeNo, String hospitalCode) {
    	boolean result = false;
    	int numAffRec = 0;
		String sqlComand = "";
		sqlComand = "INSERT INTO TRN_ONWARD " +
					"SELECT * FROM TRN_DAILY " +
					"WHERE LINE_NO = '"+lineNo+"' "+
					"AND EPISODE_NO = '"+episodeNo+"' " +
					"AND HN_NO ='"+hnNo+"' " +
					"AND IS_ONWARD = 'N' " +
					"AND HOSPITAL_CODE = '"+hospitalCode+"' ";
		numAffRec = conn2.executeUpdate(sqlComand);
		if(numAffRec > 0){
			result = true;
		}
		return result;
	}

}
